package com.base.db.mysql

import com.base.config.BaseConfig
import com.base.model.api.*
import org.apache.ibatis.annotations.Insert
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Select
import org.apache.ibatis.annotations.SelectKey
import org.apache.ibatis.annotations.Update
import org.springframework.stereotype.Repository

@Mapper
@Repository
interface MySQLMapper_App {
    @Select("""
        SELECT
            COUNT(1)
        FROM
            t_user_app u
        WHERE
            u.loginname=#{username}
    """)
    fun app_checkUserIsExistByUserName(username: String): Int

    @Select("""
        SELECT
            COUNT(1)
        FROM
            t_user_app u
        WHERE
            u.id=#{uid} and u.password=#{password}
    """)
    fun app_checkUserIsExistByUidPassword(uid: String, password: String): Int

    @SelectKey(before = false, keyProperty = "_uid", resultType = Long::class, statement = ["select last_insert_id()"])
    @Insert("""
        INSERT INTO
            t_user_app
            (loginname, password, yim_user, yim_token)
        VALUES
            (#{loginname}, #{password}, #{yim_user}, #{yim_token})
    """)
    fun app_addUserByAccountPassword(loginname: String, password: String, yim_user: String, yim_token: String, _uid: String): Int

    @SelectKey(before = false, keyProperty = "_uid", resultType = Long::class, statement = ["select last_insert_id()"])
    @Insert("""
        INSERT INTO
            t_user_app
            (phone, yim_user, yim_token)
        VALUES
            (#{phone}, #{yim_user}, #{yim_token})
    """)
    fun app_addUserByPhone(phone: String, yim_user: String, yim_token: String, _uid: String): Int

    @SelectKey(before = false, keyProperty = "_uid", resultType = Long::class, statement = ["select last_insert_id()"])
    @Insert("""
        <script>
            INSERT INTO
                t_user_app
                (${'$'}{platform}, yim_user, yim_token)
            VALUES
                (#{thirdID}, #{yim_user}, #{yim_token})
        </script>        
    """)
    fun app_addUserByThird(platform: String, thirdID: String, yim_user: String, yim_token: String, _uid: String): Int

    @Insert("""
        INSERT INTO
            t_user_app_ext
            (uid)
        VALUES
            (#{uid})
    """)
    fun app_addUserExt(uid: String): Int

    @Update("""
        UPDATE
            t_user_app u
        SET
            u.password=#{password}
        WHERE
            u.id=#{uid}
    """)
    fun app_updateUserByAccount(uid: String, password: String): Int

    @Update("""
        UPDATE
            t_user_app u
        SET
            u.phone=#{phone}
        WHERE
            u.id=#{uid}
    """)
    fun app_updateUserByPhone(uid: String, phone: String): Int

    @Update("""
        <script>
            UPDATE
                t_user_app u
            SET
                u.${'$'}{platform}=#{thirdID}
            WHERE
                u.id=#{uid}
        </script>
    """)
    fun app_updateUserByThird(uid: String, platform: String, thirdID: String): Int

    @Select("""
        SELECT
            u.id `id`
        FROM
            t_user_app u
        WHERE
            u.loginname=#{username} and u.password=#{password}
    """)
    fun app_getUserByAccount(username: String, password: String): List<Map<String, Any?>>?

    @Select("""
        SELECT
            u.id `id`
        FROM
            t_user_app u
        WHERE
            u.phone=#{phone}
    """)
    fun app_getUserByPhone(phone: String): List<Map<String, Any?>>?

    @Select("""
        <script>
            SELECT
                u.id `id`
            FROM
                t_user_app u
            WHERE
                u.${'$'}{platform}=#{thirdID}
        </script>
    """)
    fun app_getUserByThird(platform: String, thirdID: String): List<Map<String, Any?>>?

    @Select("""
        SELECT
            IF(u.phone IS NULL, '0', '1') `isBindPhone`,
            IF(u.third_id_qq IS NULL, '0', '1') `isBindThirdQQ`,
            IF(u.third_id_wechat IS NULL, '0', '1') `isBindThirdWeChat`
        FROM
            t_user_app u
        WHERE
            u.id=#{uid}
    """)
    fun app_getUserBindStatusByID(uid: String): List<BindStatusModel>?

    @Select("""
        SELECT
            u.name `name`,
            CONCAT('${BaseConfig.ossBaseURL}', u.avatar) `avatar`,
            u.yim_user `yim_user`,
            u.yim_token `yim_token`
        FROM
            t_user_app u
        WHERE
            u.id=#{uid}
    """)
    fun app_getUserInfoByUID(uid: String): List<UserModel_App>?

    @Select("""
        SELECT
            b.id `id`,
            b.title `title`,
            CONCAT('${BaseConfig.ossBaseURL}', b.img) `img`,
            b.type `type`
        FROM
            t_banner b
        WHERE
            b.position=#{position}
    """)
    fun app_getHome_banners(position: Int): List<ShouYeTJModel.ShouYeTJModel_Banner>?

    @Select("""
        SELECT
            s.id `id`,
            s.title `title`,
            CONCAT('${BaseConfig.ossBaseURL}', s.img_main) `img`,
            s.count_sale `count`,
            s.des `des`,
            s.money_min `money`,
            TRUNCATE(s.money_min / 100, 0) `money1`,
            TRUNCATE(s.money_max / 100, 0) `money2`,
            s.content_like `likeContent`
        FROM
            t_spu s
        ORDER BY
            RAND()
        LIMIT
            #{pageSize}
    """)
    fun app_getHome_productItems(pageSize: Int): List<ProductListModel.ProductListSubModel>?

    @Select("""
        SELECT
            c.id `id`,
            CONCAT('${BaseConfig.ossBaseURL}', c.img) `img`,
            c.title `title`,
            c.parent `parent`,
            c.isSelect `isSelect`
        FROM
            t_category c
    """)
    fun app_getCategory(): List<ShouYeFLModel.ShouYeFLModel_Group.ShouYeFLModel_Item>?

    @Select("""
        SELECT
            v.id `id`,
            v.uid `uid`,
            CONCAT('${BaseConfig.ossBaseURL}', v.video) `video`,
            CONCAT('${BaseConfig.ossBaseURL}', v.video, '?x-oss-process=video/snapshot,t_1000,m_fast,ar_auto') `image`
        FROM
            t_video v
        ORDER BY
            RAND()
        LIMIT
            #{pageSize}
    """)
    fun app_getVideos(pageSize: Int): List<VideoModel>?

    @Insert("""
        INSERT INTO
            t_video
            (video, uid)
        VALUES
            (TRIM(LEADING '${BaseConfig.ossBaseURL}' FROM #{video}), #{uid})
    """)
    fun app_addVideo(uid: String, video: String): Int

    @Update("""
        UPDATE
            t_user_app u
        SET
            u.avatar=TRIM(LEADING '${BaseConfig.ossBaseURL}' FROM #{avatar})
        WHERE
            u.id=#{uid}
    """)
    fun app_updateUserAvatar(uid: String, avatar: String): Int

    @Update("""
        UPDATE
            t_user_app u
        SET
            u.name=#{name}
        WHERE
            u.id=#{uid}
    """)
    fun app_updateUserName(uid: String, name: String): Int

    @Select("""
        SELECT
            s.title `title`,
            s.money_min `money_min`,
            s.money_max `money_max`,
            CONVERT(s.count_sale, CHAR) `count_sale`,
            CONCAT('${BaseConfig.ossBaseURL}', s.img_main) `img_main`,
            s.img_banner `img_banner`,
            CONCAT('${BaseConfig.ossBaseURL}', s.video) `video`,
            s.img_detail `img_detail`,
            s.params_pinpai `params_pinpai`,
            s.params_xinghao `params_xinghao`,
            s.params_tuihuan `params_tuihuan`
        FROM
            t_spu s
        WHERE
            s.id=#{id}
    """)
    fun app_getSPUDetailById(id: String): List<HashMap<String, Any>>?

    @Select("""
        SELECT
            s.id `id`,
            s.title `title`,
            CONCAT('${BaseConfig.ossBaseURL}', s.img) `img`,
            s.money `money`,
            s.count_left `count_left`,
            s.count_sales `count_sales`
        FROM
            t_sku s
        WHERE
            s.spuid=#{id}
    """)
    fun app_getSKUsBySPUId(id: String): List<Map<String, Any?>>?

    @Select("""
        SELECT
            s.name `name`,
            CONCAT('${BaseConfig.ossBaseURL}', s.avatar) avatar,
            s.account account
        FROM
            t_service s
    """)
    fun app_getService(): List<Map<String, Any?>>?

    @Update("""
        <script>
            UPDATE
                t_user_app_ext e
            SET
                e.uid=#{uid}
                <if test="carts != null">
                    ,e.carts=#{carts}
                </if>
                <if test="collections != null">
                    ,e.collections=#{collections}
                </if>
            WHERE
                e.uid=#{uid}
        </script>
    """)
    fun app_updateUserExtById(uid: String, carts: String? = null, collections: String? = null): Int

    @Select("""
        SELECT
            e.id `id`,
            e.collections `collections`,
            e.carts `carts`
        FROM
            t_user_app_ext e
        WHERE
            e.uid=#{uid}
    """)
    fun app_getUserExtById(uid: String): List<Map<String, Any?>>?

    @Select("""
        <script>
            SELECT
                sku.spuid `spu`,
                sku.id `sku`,
                sku.money `money`,
                CONCAT('${BaseConfig.ossBaseURL}', sku.img) `img`,
                sku.title `titleType`,
                (SELECT spu.title FROM t_spu spu WHERE spu.id=sku.spuid) `titleProduct`,
                sku.count_left `countLeft`
            FROM
                t_sku sku
            WHERE
                sku.id IN
                <foreach item='item' index='index' collection='skus' open='(' separator=',' close=')'>
                    #{item}
                </foreach>
        </script>
    """)
    fun app_getCartBySku(skus: List<String>): List<CartListModel.CartMainModel>?

    @Select("""
        SELECT
            COUNT(1)
        FROM
            t_reply r
        WHERE
            r.spu=#{spu}
    """)
    fun app_getReplyCountBySku(spu: String): Int?

    @Select("""
        SELECT 
            r.content `content`,
            r.date `date`,
            r.imgs `imgs`,
            u.avatar `avatar`,
            u.name `name`,
            spu.title `spu`,
            sku.title `sku`
        FROM 
            t_reply r,
            t_user_app u,
            t_spu spu,
            t_sku sku
        WHERE
            r.spu=#{spu}
            and r.uid=u.id 
            and r.spu=spu.id 
            and r.sku=sku.id
        LIMIT
            #{_pageOffset}, #{pageSize}
    """)
    fun app_getReplysBySku(spu: String, page: Int, pageSize: Int, _pageOffset:Int = (page - 1) * pageSize): List<ReplyModel>?

    @Select("""
        <script>
            SELECT
                spu.id `id`,
                CONCAT('${BaseConfig.ossBaseURL}', spu.img_main) `img`,
                spu.title `title`,
                spu.money_min `money1`,
                spu.money_max `money2`,
                spu.count_sale `count`,
                spu.des `des`,
                spu.content_like `likeContent`
            FROM 
                t_spu spu
            WHERE
                spu.title like CONCAT('%', #{content}, '%')
            ORDER BY
                <choose>
                    <when test="orderBy == '1'">
                        spu.id asc
                    </when>
                    <when test="orderBy == '2'">
                        spu.count_sale asc
                    </when>
                    <when test="orderBy == '3'">
                        spu.money_min desc
                    </when>
                    <when test="orderBy == '4'">
                        spu.money_min asc
                    </when>
                    <otherwise>
                        spu.id asc
                    </otherwise>
                </choose>
            LIMIT
                #{_pageOffset}, #{pageSize}
        </script>            
    """)
    fun app_getSPUs(content: String?, page: Int, pageSize: Int, orderBy: String, type: String?, _pageOffset:Int = (page - 1) * pageSize): List<ProductListModel.ProductListSubModel>?
}
